package com.bothin.experiment4.dao;

import com.bothin.experiment4.dto.ProductFormDTO;
import com.bothin.experiment4.model.ProductForm;
import org.apache.ibatis.annotations.Select;
import tk.mybatis.mapper.common.Mapper;

import java.util.List;

public interface ProductFormMapper extends Mapper<ProductForm> {

    @Select("SELECT * FROM `product_form` WHERE form_type = #{type}")
    public List<ProductForm> findAllByType(Integer type);

    @Select("SELECT * FROM `product_form` WHERE form_type = #{type} AND product_quantity < 0")
    public List<ProductForm> findAllProductQuantityLessThanZero(Integer type);

    @Select("SELECT product_form.form_id formId,product_form.contact_id contactId,product_form.product_id productId,product_form.product_quantity productQuantity,\n" +
            "product_form.form_type formType,product_form.finsh_status finshStatus,customer.customer_name customerName,customer.customer_address customerAddress,\n" +
            "product.product_name productName,product.product_icon productIcon,product.product_stock productStock\n" +
            "FROM ( (`product_form` LEFT JOIN contact on product_form.contact_id = contact.contact_id ) \n" +
            "LEFT JOIN product ON product_form.product_id = product.product_id ) LEFT JOIN customer on contact.customer_id = customer.customer_id\n" +
            "WHERE product_form.form_type = #{type}")
    public List<ProductFormDTO> findAllByTypeDTO(Integer type);

    @Select("SELECT product_form.form_id formId,product_form.contact_id contactId,product_form.product_id productId,product_form.product_quantity productQuantity,\n" +
            "product_form.form_type formType,product_form.finsh_status finshStatus,customer.customer_name customerName,customer.customer_address customerAddress,\n" +
            "product.product_name productName,product.product_icon productIcon,product.product_stock productStock\n" +
            "FROM ( (`product_form` LEFT JOIN contact on product_form.contact_id = contact.contact_id ) \n" +
            "LEFT JOIN product ON product_form.product_id = product.product_id ) LEFT JOIN customer on contact.customer_id = customer.customer_id\n" +
            "WHERE product_form.form_type = #{type} AND product_form.finsh_status = 0 ")
    public List<ProductFormDTO> findAllByTypeAndUnhandleDTO(Integer type);

    @Select("SELECT product_form.form_id formId,product_form.contact_id contactId,product_form.product_id productId,product_form.product_quantity productQuantity,\n" +
            "product_form.form_type formType,product_form.finsh_status finshStatus,customer.customer_name customerName,customer.customer_address customerAddress,\n" +
            "product.product_name productName,product.product_icon productIcon,product.product_stock productStock\n" +
            "FROM ( (`product_form` LEFT JOIN contact on product_form.contact_id = contact.contact_id ) \n" +
            "LEFT JOIN product ON product_form.product_id = product.product_id ) LEFT JOIN customer on contact.customer_id = customer.customer_id\n" +
            "WHERE product_form.form_type = #{type} AND product_form.finsh_status != 0 ")
    public List<ProductFormDTO> findAllByTypeAndHandledDTO(Integer type);

    @Select("SELECT product_form.form_id formId,product_form.contact_id contactId,product_form.product_id productId,product_form.product_quantity productQuantity,\n" +
            "product_form.form_type formType,product_form.finsh_status finshStatus,customer.customer_name customerName,customer.customer_address customerAddress,\n" +
            "product.product_name productName,product.product_icon productIcon,product.product_stock productStock\n" +
            "FROM ( (`product_form` LEFT JOIN contact on product_form.contact_id = contact.contact_id ) \n" +
            "LEFT JOIN product ON product_form.product_id = product.product_id ) LEFT JOIN customer on contact.customer_id = customer.customer_id\n" +
            "WHERE product_form.form_type = #{type} " +
            "AND ( product_form.contact_id LIKE #{key} OR product_form.form_id LIKE #{key} )")
    public List<ProductFormDTO> findAllByTypeAndKeywordDTO(Integer type,String key);

    @Select("SELECT product_form.form_id formId,product_form.contact_id contactId,product_form.product_id productId,product_form.product_quantity productQuantity,\n" +
            "product_form.form_type formType,product_form.finsh_status finshStatus,customer.customer_name customerName,customer.customer_address customerAddress,\n" +
            "product.product_name productName,product.product_icon productIcon,product.product_stock productStock\n" +
            "FROM ( (`product_form` LEFT JOIN contact on product_form.contact_id = contact.contact_id ) \n" +
            "LEFT JOIN product ON product_form.product_id = product.product_id ) LEFT JOIN customer on contact.customer_id = customer.customer_id\n" +
            "WHERE product_form.form_id = #{id}")
    public ProductFormDTO findOneByIdDTO(String id);

    @Select("SELECT product_form.form_id formId,product_form.contact_id contactId,product_form.product_id productId,product_form.product_quantity productQuantity,\n" +
            "product_form.form_type formType,product_form.finsh_status finshStatus,customer.customer_name customerName,customer.customer_address customerAddress,\n" +
            "product.product_name productName,product.product_icon productIcon,product.product_stock productStock\n" +
            "FROM ( (`product_form` LEFT JOIN contact on product_form.contact_id = contact.contact_id ) \n" +
            "LEFT JOIN product ON product_form.product_id = product.product_id ) LEFT JOIN customer on contact.customer_id = customer.customer_id\n" +
            "WHERE product_form.contact_id = #{contactId}")
    public List<ProductFormDTO> findByContactIdDTO(String contactId);

    @Select("SELECT finsh_status finshStatus FROM `product_form` \n" +
            "WHERE contact_id = #{contactId}")
    public List<Integer> findAllFinshStatusByContactId(String contactId);

}